﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using OBUCare.Core.Models;
using OBUCare.Core.DataAccess;
using Telerik.Web.Mvc;
using OBUCare.Web.Models;
using OBUCare.Web.Infrastructure;
using OBUCare.Core.BL;
using OBUCare.Core.Common;

namespace OBUCare.Web.Controllers
{
    /// <summary>
    /// 报表
    /// </summary>
    public class ReportController : Controller
    {
        OBUCareOrderMgr obuMgr = new OBUCareOrderMgr();

        private OBU_CAREContext db = new OBU_CAREContext();

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            obuMgr.Dispose();
        }

        public ActionResult Index()
        {
            return RedirectToAction("Daily");
        }

        public ActionResult ParentPage()
        {
            return View();
        }

        #region Daily 营业厅维修业务日报表

        public ActionResult Daily()
        {
            return View();
        }


        /// <summary>
        /// 根据sql返回查询后操作员每日发票统计
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListOperatorInvoicesSqlItems(string sql, string searchSql)
        {
            var result = getOperatorInvoices(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }

        /// <summary>
        /// 根据sql返回查询后操作员每日发票统计数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <returns></returns>
        private IEnumerable<View_Operator_InvoiceInfos> getOperatorInvoices(string sql, string searchSql)
        {
            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            //如果营业厅的权限为本营业厅 则界面默认显示当前登录员的操作发票统计信息
            // string strOperator = whereAgentNo == "" ? "" : " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);

            string normalSql = tempSql.Trim() == "" ?
              " [RECORD_TIME]>='" + DateTime.Now.ToShortDateString() + "' AND [RECORD_TIME] <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
              "";//如果没有任何条件 则默认显示当天的所有信息

            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            var result = obuMgr.GetOperatorInvoices(tempSql + normalSql + whereAgentNo + strOperator);
            return result;
        }

        /// <summary>
        /// 根据sql返回查询后操作员每日收款退款统计
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListOperatorReceivedReturnedMoneySqlItems(string sql, string searchSql)
        {

            var result = getOperatorReceivedReturnedMoney(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }

        /// <summary>
        /// 根据sql返回查询后操作员每日收款退款统计数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <returns></returns>
        private IEnumerable<View_Received_Returned_Money> getOperatorReceivedReturnedMoney(string sql, string searchSql)
        {
            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            ////如果营业厅的权限为本营业厅 则界面默认显示当前登录员的操作发票统计信息
            //string strOperator = whereAgentNo == "" ? "" : " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);
            string normalSql = tempSql.Trim() == "" ?
            " [RECORD_TIME]>='" + DateTime.Now.ToShortDateString() + "' AND [RECORD_TIME] <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
            "";//如果没有任何条件 则默认显示当天的所有信息

            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            var result = obuMgr.GetOperatorReceivedReturnedMoney(tempSql + normalSql + whereAgentNo + strOperator);
            return result;
        }

        [HttpPost]
        public ActionResult DailySearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Operator, string Agent,
            string txtOperator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var sql = obuMgr.GetSearhDailyReportSql(FromCreatedDate, ToCreatedDate,
                    Operator.ChangAllTextToNull(), Agent.ChangAllTextToNull(), txtOperator.Trim().ChangAllTextToNull());

                //查询今日是否还有未提交的流水
                string whereAgentNo = Agent == null || Agent == "" ? "" : " AND postInfo.AGENT_NO='" + Agent + "'";
                string specialCondtion = " AND RECORD_TIME>='" + DateTime.Now.ToShortDateString() + "'" + " AND RECORD_TIME<'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " + " AND prints.ID IS NULL ";
                int count = new TransactionController().getTransLogs(whereAgentNo + specialCondtion).Count();
                string msg = "";
                if (count > 0) msg = "今日还有未提交流水的维修业务";

                return Json(new { Sql = sql, Success = true, ErrorMsg = msg });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }

        public ActionResult PrintDaily(string sql, string DailySql, DateTime? StartDate, DateTime? EndDate, string agentNo)
        {
            DateTime? ToCreatedDate = EndDate;
            DateTime? FromCreatedDate = StartDate;

            ViewBag.recordDate = FromCreatedDate.ToText(UIConstants.DateFormatString) + "~" + ToCreatedDate.ToText(UIConstants.DateFormatString);
            ViewBag.agent_name = UIUtility.GetCurrentUser().AgentName;

            #region 导出发票报表部分
            var operatorInvoices = getOperatorInvoices(sql, DailySql);
            List<View_Operator_InvoiceInfos> agentOperatorInvoices = null;
            if (operatorInvoices != null && operatorInvoices.Count() > 0)
            {
                agentOperatorInvoices = (from i in operatorInvoices
                                         // where i.AGENT_NO == agentNo
                                         select i).ToList();
                //写入统计日期
                string sumaryDate = "全部";
                if (ToCreatedDate != null && FromCreatedDate != null)
                {
                    sumaryDate = string.Format("{0}~{1}", FromCreatedDate.ToText(UIConstants.DateFormatString), ToCreatedDate.ToText(UIConstants.DateFormatString));
                }
                else
                {
                    sumaryDate = ToCreatedDate.HasValue ? ToCreatedDate.ToText(UIConstants.DateFormatString) : FromCreatedDate.ToText(UIConstants.DateFormatString);
                }
                if (sumaryDate.IsNullOrEmpty())
                {
                    sumaryDate = DateTime.Now.ToShortDateString() + "~" + DateTime.Now.ToShortDateString();
                }
                ViewBag.recordDate = sumaryDate;
                if (agentOperatorInvoices.Count > 0)
                {


                    ViewBag.agent_name = agentOperatorInvoices[0].AGENT_NAME; //营业厅名称


                    View_Operator_InvoiceInfos ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "维修费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentOperatorInvoices[0].AGENT_NAME,
                            FeeType = "维修费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "服务费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentOperatorInvoices[0].AGENT_NAME,
                            FeeType = "服务费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "押金");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentOperatorInvoices[0].AGENT_NAME,
                            FeeType = "押金"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }
                    //var agentNew=agentOperatorInvoices.GroupBy(a=>a.FeeType).ToList();
                    List<View_Operator_InvoiceInfos> sumAgentOperatorinoivce = new List<View_Operator_InvoiceInfos>();

                    var typeCount = agentOperatorInvoices.Select(a => a.FeeType).Distinct();
                    for (int i = 0; i < typeCount.Count(); i++)
                    {

                        View_Operator_InvoiceInfos v = new View_Operator_InvoiceInfos();
                        switch (typeCount.ElementAt(i))
                        {
                            case "维修费":
                                var agentNew = agentOperatorInvoices.Where(w => w.FeeType.Equals("维修费")).ToList();
                                v.AGENT_NAME = agentNew.FirstOrDefault().AGENT_NAME;
                                v.AGENT_NO = agentNew.FirstOrDefault().AGENT_NO;
                                v.FeeType = "维修费";
                                v.FUND_AMOUNT = agentNew.Sum(b => b.FUND_AMOUNT);
                                v.INVOICE_TYPE = agentNew.FirstOrDefault().INVOICE_TYPE;
                                v.InvoiceCount = agentNew.Sum(b => b.InvoiceCount);
                                v.Amount = agentNew.Sum(b => b.Amount);
                                break;
                            case "服务费":
                                var agentService = agentOperatorInvoices.Where(w => w.FeeType.Equals("服务费")).ToList();
                                v.AGENT_NAME = agentService.FirstOrDefault().AGENT_NAME;
                                v.AGENT_NO = agentService.FirstOrDefault().AGENT_NO;
                                v.FeeType = "服务费";
                                v.FUND_AMOUNT = agentService.Sum(b => b.FUND_AMOUNT);
                                v.INVOICE_TYPE = agentService.FirstOrDefault().INVOICE_TYPE;
                                v.InvoiceCount = agentService.Sum(b => b.InvoiceCount);
                                v.Amount = agentService.Sum(b => b.Amount);
                                break;
                            case "押金":
                                var agentYajin = agentOperatorInvoices.Where(w => w.FeeType.Equals("押金")).ToList();
                                v.AGENT_NAME = agentYajin.FirstOrDefault().AGENT_NAME;
                                v.AGENT_NO = agentYajin.FirstOrDefault().AGENT_NO;
                                v.FeeType = "押金";
                                v.FUND_AMOUNT = agentYajin.Sum(b => b.FUND_AMOUNT);
                                v.INVOICE_TYPE = agentYajin.FirstOrDefault().INVOICE_TYPE;
                                v.InvoiceCount = agentYajin.Sum(b => b.InvoiceCount);
                                v.Amount = agentYajin.Sum(b => b.Amount);
                                break;
                        }
                        sumAgentOperatorinoivce.Add(v);
                    }
                    agentOperatorInvoices = sumAgentOperatorinoivce;
                }

            }
            #endregion
            #region 导出收退款的报表
            var moneyItems = getOperatorReceivedReturnedMoney(sql, DailySql);

            var agentMoneyItems = (from m in moneyItems
                                   // where m.AGENT_NO == agentNo
                                   select m).ToList();

            decimal from_0 = 0; ;
            decimal from_1 = 0;
            decimal from_2 = 0;
            decimal from_3 = 0;
            decimal from_4 = 0;
            decimal from_5 = 0;
            decimal from_10 = 0;
            decimal all_amount = 0;
            decimal ReturnedAmount = 0;
            decimal ReturnToFinance = 0;
            decimal ReturnedAllAmount = 0;

            if (moneyItems.Count() > 0)
            {
                var agentMoneyGroup = from m in agentMoneyItems
                                      group m by m.AGENT_NO into g
                                      select g;

                agentMoneyItems.Each(v =>
                {
                    from_0 += Convert.ToDecimal(v.from_0);
                    from_1 += Convert.ToDecimal(v.from_1);
                    from_2 += Convert.ToDecimal(v.from_2);
                    from_3 += Convert.ToDecimal(v.from_3);
                    from_4 += Convert.ToDecimal(v.from_4);
                    from_5 += Convert.ToDecimal(v.from_5);
                    from_10 += Convert.ToDecimal(v.from_10);
                    all_amount += Convert.ToDecimal(v.AmountAll);
                    ReturnedAmount += Convert.ToDecimal(v.ReturnedAmount);
                    ReturnToFinance += Convert.ToDecimal(v.ReturnToFinance);
                    ReturnedAllAmount += Convert.ToDecimal(v.ReturnedAllAmount);

                });
            }
            ViewBag.from_0 = from_0;
            ViewBag.from_1 = from_1;
            ViewBag.from_2 = from_2;
            ViewBag.from_3 = from_3;
            ViewBag.from_4 = from_4;
            ViewBag.from_5 = from_5;
            ViewBag.from_10 = from_10;
            ViewBag.AllAmount = all_amount;
            ViewBag.returnAmount = ReturnedAmount;
            ViewBag.returnFinace = ReturnToFinance;
            ViewBag.returnAll = ReturnedAllAmount;
            ViewBag.Operator = UIUtility.GetCurrentUser().OPERATOR_NAME;
            #endregion

            return View(agentOperatorInvoices);


        }
        #endregion

        #region  导出Excel
        public ActionResult ExportDaily(string sql, string searchSql, DateTime? FromCreatedDate, DateTime? ToCreatedDate)
        {
            string exportFileTitle = "营业厅维修日报.xls";
            ActionResult result = null;
            try
            {
                //根据编号判断查询权限//如果营业厅的权限为所有 
                string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : Request["agentNO"].ToString();
                string template = "";
                ExportHelper export = null;
                OPERATOR u = UIUtility.GetCurrentUser();
                //u.AgentNo = "110100010002";
                if (whereAgentNo == "") //如果是速通登录 todo:
                {

                    template = Server.MapPath("/Templates/速通日报.xls");
                    export = new ExportHelper(template);

                    //导出第一个Sheet
                    exportDailyReport(export, sql, searchSql, FromCreatedDate, ToCreatedDate, 0);

                    //导出第2个Sheet
                    exportDailyReport(export, sql, searchSql, FromCreatedDate, ToCreatedDate, 1);

                }
                else //非速通，则导出营业厅报表
                {
                    template = Server.MapPath("/Templates/营业厅日报.xls");
                    export = new ExportHelper(template);
                    exportAgentDailyReport(export, sql, searchSql, FromCreatedDate, ToCreatedDate, u.AgentNo);
                }

                result = export.DownLoad(this.File, exportFileTitle);
            }
            catch (Exception ex)
            {
                result = ExportHelper.HandleExportException(this, ex, exportFileTitle);
            }
            return result;
        }

        /// <summary>
        /// 导出速通日报的第一个Sheet
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private void exportDailyReport(ExportHelper export, string sql, string searchSql, DateTime? FromCreatedDate, DateTime? ToCreatedDate, int sheetIndex)
        {

            int startRowIndex = 3;

            int printDateIndex = sheetIndex == 0 ? 9 : 11;

            if (sheetIndex == 0) //导出发票报表的Sheet
            {
                var operatorInvoices = getOperatorInvoices(sql, searchSql).ToList();
                export.LastRowIndex = startRowIndex;
                if (operatorInvoices.Count > 0)
                {
                    //将合计行往后移动数据总行数一1
                    export.ExportAddionalInfomation((excel, ep) =>
                    {
                        excel.ActiveSheetIndex = sheetIndex;
                        excel.ShiftRows(4, 5, operatorInvoices.Count() - 1);
                    });

                    //导出主要数据
                    export.ExportToExcel(operatorInvoices, (item, excel, rowIndex) =>
                    {
                        int colIndex = 0;

                        excel[rowIndex, colIndex++] = item.RECORD_TIME.ToText(UIConstants.DateFormatString);
                        excel[rowIndex, colIndex++] = item.AGENT_NAME.ToText();
                        excel[rowIndex, colIndex++] = item.OPERATOR_NAME.ToText();
                        excel[rowIndex, colIndex++] = item.FeeType.ToText();
                        excel.SetCellValue(rowIndex, colIndex++, item.FUND_AMOUNT);
                        excel.SetCellValue(rowIndex, colIndex++, item.StatetaxCount);
                        excel.SetCellValue(rowIndex, colIndex++, item.StatetaxAmount);
                        excel.SetCellValue(rowIndex, colIndex++, item.LocaltaxCount);
                        excel.SetCellValue(rowIndex, colIndex++, item.LocaltaxAmount);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReceipttaxCount);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReceipttaxAmount);

                        //设置样式
                        for (int c = 0; c < colIndex; c++)
                        {
                            excel.SetCellStyle(startRowIndex, c, rowIndex, c);
                        }

                    }, sheetIndex, startRowIndex);

                }
            }
            else //导出收退款的报表
            {
                var moneyItems = getOperatorReceivedReturnedMoney(sql, searchSql).ToList();
                export.LastRowIndex = startRowIndex;
                if (moneyItems.Count > 0)
                {



                    //将合计行往后移动数据总行数一1
                    export.ExportAddionalInfomation((excel, ep) =>
                    {
                        excel.ActiveSheetIndex = sheetIndex;
                        excel.ShiftRows(4, 5, moneyItems.Count() - 1);
                    });

                    //导出主要数据
                    export.ExportToExcel(moneyItems, (item, excel, rowIndex) =>
                    {
                        int colIndex = 0;

                        excel[rowIndex, colIndex++] = item.RECORD_TIME.ToText(UIConstants.DateFormatString);
                        excel[rowIndex, colIndex++] = item.AGENT_NAME.ToText();
                        excel[rowIndex, colIndex++] = item.OPERATOR_NAME.ToText();
                        excel.SetCellValue(rowIndex, colIndex++, item.from_0);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_1);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_2);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_3);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_4);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_5);
                        excel.SetCellValue(rowIndex, colIndex++, item.from_10);
                        excel.SetCellValue(rowIndex, colIndex++, item.AmountAll);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReturnedAmount);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReturnToFinance);
                        excel.SetCellValue(rowIndex, colIndex++, item.ReturnedAllAmount);

                        //设置样式
                        for (int c = 0; c < colIndex; c++)
                        {
                            excel.SetCellStyle(startRowIndex, c, rowIndex, c);
                        }

                    }, sheetIndex, startRowIndex);
                }
            }

            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                excel.ActiveSheetIndex = sheetIndex;
                //写入统计日期
                string sumaryDate = "全部";
                if (ToCreatedDate != null && FromCreatedDate != null)
                {
                    sumaryDate = string.Format("{0}~{1}", FromCreatedDate.ToText(UIConstants.DateFormatString), ToCreatedDate.ToText(UIConstants.DateFormatString));
                }
                else
                {
                    sumaryDate = ToCreatedDate.HasValue ? ToCreatedDate.ToText(UIConstants.DateFormatString) : FromCreatedDate.ToText(UIConstants.DateFormatString);
                }
                if (sumaryDate.IsNullOrEmpty())
                {
                    sumaryDate = DateTime.Now.ToShortDateString() + "~" + DateTime.Now.ToShortDateString();
                }
                excel[1, 1] = sumaryDate;

                int startColIndex = sheetIndex == 0 ? 4 : 3;
                int endColIndex = sheetIndex == 0 ? 10 : 12;

                //写入合计信息
                for (int i = startColIndex; i <= endColIndex; i++)
                {
                    Char c = ('A'.CharToAscii() + i).AsciiToChar();

                    string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                    excel.SetCellFormula(ep.LastRowIndex + 1, i, formula);
                }
                //excel[++ep.LastRowIndex, 0] = "合计：";
                ep.LastRowIndex += 2;
                //写入打印时间
                //excel[++ep.LastRowIndex, printDateIndex] = "打印时间：";
                if (sheetIndex == 0)
                {
                    excel[ep.LastRowIndex, printDateIndex + 1] = DateTime.Now.ToString(UIConstants.DateFormatString);
                }
                else
                {
                    excel[ep.LastRowIndex, printDateIndex + 2] = DateTime.Now.ToString(UIConstants.DateFormatString);
                }

            });
        }


        /// <summary>
        /// 导出营业厅日报
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private void exportAgentDailyReport(ExportHelper export, string sql, string searchSql, DateTime? FromCreatedDate, DateTime? ToCreatedDate, string agentNo)
        {


            int startRowIndex = 3; //起始列

            int printDateIndex = 9;//打印日期列

            string agentName = ""; //营业厅

            #region 导出发票报表部分
            var operatorInvoices = getOperatorInvoices(sql, searchSql);
            if (operatorInvoices != null && operatorInvoices.Count() > 0)
            {
                var agentOperatorInvoices = (from i in operatorInvoices
                                             where i.AGENT_NO == agentNo
                                             select i).ToList();

                if (agentOperatorInvoices.Count > 0)
                {

                    agentName = agentOperatorInvoices[0].AGENT_NAME; //营业厅名称


                    View_Operator_InvoiceInfos ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "维修费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentName,
                            FeeType = "维修费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "服务费");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentName,
                            FeeType = "服务费"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    ivInfo = agentOperatorInvoices.FirstOrDefault(iv => iv.FeeType == "押金");
                    if (ivInfo == null)
                    {
                        ivInfo = new View_Operator_InvoiceInfos
                        {
                            AGENT_NAME = agentName,
                            FeeType = "押金"
                        };
                        agentOperatorInvoices.Add(ivInfo);
                    }

                    var agentInvoiceGroup = (from i in agentOperatorInvoices
                                             group i by i.FeeType into g
                                             select g).ToList();



                    //导出主要数据
                    export.ExportToExcel(agentInvoiceGroup, (item, excel, rowIndex) =>
                    {
                        int colIndex = 0;

                        if (!item.Key.IsNullOrEmpty())
                        {
                            excel[rowIndex, colIndex] = item.Key.ToText();
                            colIndex = 4;
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.Amount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.StatetaxCount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.StatetaxAmount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.LocaltaxCount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.LocaltaxAmount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReceipttaxCount));
                            excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReceipttaxAmount));
                        }
                    }, 0, startRowIndex);
                }
            }
            else
            {
                Content("<script>alert('数据为空，无法操作导出')</script>");
            }

            #endregion



            #region 导出收退款的报表
            var moneyItems = getOperatorReceivedReturnedMoney(sql, searchSql);

            var agentMoneyItems = (from m in moneyItems
                                   where m.AGENT_NO == agentNo
                                   select m).ToList();

            if (moneyItems.Count() > 0)
            {
                var agentMoneyGroup = from m in agentMoneyItems
                                      group m by m.AGENT_NO into g
                                      select g;

                //导出主要数据
                export.ExportToExcel(agentMoneyGroup, (item, excel, rowIndex) =>
                {
                    int colIndex = 0;
                    rowIndex = 7;


                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_0));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_1));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_2));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_3));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_4));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_5));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.from_10));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.AmountAll));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReturnedAmount));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReturnToFinance));
                    excel.SetCellValue(rowIndex, colIndex++, item.Sum(v => v.ReturnedAllAmount));
                }, 0, startRowIndex);
            }
            #endregion



            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                //写入统计日期
                string sumaryDate = "全部";
                if (ToCreatedDate != null && FromCreatedDate != null)
                {
                    sumaryDate = string.Format("{0}~{1}", FromCreatedDate.ToText(UIConstants.DateFormatString), ToCreatedDate.ToText(UIConstants.DateFormatString));
                }
                else
                {
                    sumaryDate = ToCreatedDate.HasValue ? ToCreatedDate.ToText(UIConstants.DateFormatString) : FromCreatedDate.ToText(UIConstants.DateFormatString);
                }
                if (sumaryDate.IsNullOrEmpty())
                {
                    sumaryDate = DateTime.Now.ToShortDateString() + "~" + DateTime.Now.ToShortDateString();
                }
                excel[1, 1] = sumaryDate;

                //写入营业厅                
                excel[1, 4] = agentName;
                //写入操作员
                excel[1, 7] = UIUtility.GetCurrentUser().OPERATOR_NAME;

                //写入打印时间
                excel[8, printDateIndex] = "打印时间：";
                excel[8, printDateIndex + 1] = DateTime.Now.ToString(UIConstants.DateFormatString);

            });
        }

        #endregion

        #region 发票报表

        public ActionResult Invoice()
        {
            return View();
        }


        /// <summary>
        /// 根据维修单状态，返回维修单，为空则返回全部
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListInvoiceSqlItems(string sql, string searchSql)
        {

            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND OPERATOR_NO='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);

            string normalSql = tempSql.Trim() == "" ?
              " [INCOICE_TIME]>='" + DateTime.Now.ToShortDateString() + "' AND [INCOICE_TIME] <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
              "";//如果没有任何条件 则默认显示当天的所有信息

            var result = obuMgr.GetInvoices(tempSql + normalSql + whereAgentNo + strOperator);
            var temp = new GridModel(result);
            return View(temp);
        }

        [HttpPost]
        public ActionResult InvoiceSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string InvoiceType,
            string UserNo, string SERIAL_NO, string Operator, string AgentType, string Agent, string txtOperator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var sql = obuMgr.GetSearhInvoiceReportSql(FromCreatedDate, ToCreatedDate,
                    InvoiceType.ChangAllTextToNull(), UserNo.ChangAllTextToNull(), SERIAL_NO.ChangAllTextToNull(),
                    Operator.ChangAllTextToNull(), AgentType.ChangAllTextToNull(), Agent.ChangAllTextToNull(), txtOperator.Trim().ChangAllTextToNull());

                return Json(new { Sql = sql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }


        #endregion

        #region 交易资金来源报表 Transaction

        public ActionResult Transaction()
        {
            return View();
        }


        /// <summary>
        /// 根据维修单状态，返回维修单，为空则返回全部
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [GridAction]
        public ActionResult ListTransactionSqlItems(string sql, string searchSql)
        {
            //根据编号判断查询权限
            string whereAgentNo = Request["agentNO"] == null || Request["agentNO"].ToString() == "" ? "" : " AND AGENT_NO='" + Request["agentNO"].ToString() + "'";
            string modelID = Request["menuNames"] == null || Request["menuNames"].ToString() == "" ? "" : Request["menuNames"].ToString();
            //如果权限为本营业厅操作员权限 则增加登录员查询条件
            OPERATOR operatorInfo = UIUtility.GetCurrentUser();
            string models = UIUtility.getOperatorByModule(operatorInfo, Convert.ToInt32(modelID));
            string strOperator = "";
            if (models == "6")
                strOperator = " AND tLogOperator='" + UIUtility.GetCurrentUser().OPERATOR_NO + "' ";

            string tempSql = "";
            if ((!sql.IsNullOrEmpty()) && (!searchSql.IsNullOrEmpty()))
                tempSql = sql + " and " + searchSql;
            else
                tempSql = string.Format("{0} {1}", sql, searchSql);

            string normalSql = tempSql.Trim() == "" ?
                " RECORD_TIME>='" + DateTime.Now.ToShortDateString() + "' AND RECORD_TIME <'" + DateTime.Now.AddDays(1).ToShortDateString() + "' " :
                "";//如果没有任何条件 则默认显示当天的所有信息
            var result = obuMgr.GetTransactions(tempSql + normalSql + whereAgentNo + strOperator).ToList();
            try
            {


                //获取流水单ID
                var serialids = (from i in result
                                 select i.SERIAL_ID).ToArray();
                //根据流水单ID获取资金来源信息
                var fundDetailResult = (from i in new OBU_CAREContext().FUND_DETAIL.Include("FUND_INFO")
                                        where i.FUND_INFO.ID == i.FUND_ID && serialids.Contains(i.SERIAL_ID)
                                        select i).ToList();
                //将资金来源信息合并
                result.Each(r =>
                {
                    if (r.SERIAL_ID != null && r.SERIAL_ID.ToText() != "")
                    {
                        var fund = (from i in fundDetailResult
                                    where i.SERIAL_ID == long.Parse(r.SERIAL_ID.ToString())
                                    select i).ToList();
                        string fundInfo = "";
                        string fundremark = "";
                        fund.Each(f =>
                        {
                            fundInfo += new ConfigMgr().GetTypeValue(f.FUND_INFO.FUND_FROM, ConfigMgr.tblFundType).DICT_TITLE + ":" + f.FUND_INFO.AMOUNT + " ";
                            fundremark += f.FUND_INFO.REMARK + " ";
                        });
                        r.FUNDINFO = fundInfo;
                        r.FUND_REMARK = fundremark;
                    }
                });

                var temp = new GridModel(result);
                return View(temp);

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        [HttpPost]
        public ActionResult TransactionSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string TransactionType,
            string UserNo, string SERIAL_NO, string Operator, string AgentType, string Agent, string txtOperator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var sql = obuMgr.GetSearhTransactionReportSql(FromCreatedDate, ToCreatedDate,
                    TransactionType.ChangAllTextToNull(), UserNo.Trim().ChangAllTextToNull(),
                    SERIAL_NO.Trim().ChangAllTextToNull(), Operator.ChangAllTextToNull(), AgentType.ChangAllTextToNull(),
                    Agent.ChangAllTextToNull(), txtOperator.Trim().ChangAllTextToNull());

                return Json(new { Sql = sql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }


        #endregion

        #region 厂商报价查询报表
        public ActionResult SupplierPriceReport()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListSupplierPrice(string sql, string searchSql)
        {
            var result = getList(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }
        /// <summary>
        /// 获取结果集
        /// </summary>
        /// <param name="sDate"></param>
        /// <param name="eDate"></param>
        /// <param name="condtion"></param>
        /// <returns></returns>
        public IEnumerable<SupplierPriceViewModel> getList(string sql, string searchSql)
        {
            string sqlContent = "select a.SUPPLIER_ID,SHORTNAME,min(sumprice) MIN_PRICE,max(sumprice)MAX_PRICE,"
                + "CONVERT(DECIMAL(8,2),sum(SUMPRICE)/count(1)) AVG_PRICE,sum(sumprice) SUM_PRICE,"
                + " count(1)SUM_COUNT from ("
                + " SELECT b.supplier_id,b.repair_order_id,E.SHORTNAME,sum(price) sumprice FROM ("
                + " SELECT DISTINCT REPAIR_ORDER_ID  FROM REPAIR_ORDER_TRACKING "
                + " WHERE REPAIR_ORDER_STATUS IN (10,11,12,13,14) " + searchSql + ") AS A "
                + " INNER JOIN REPAIR_ORDER_PRICE AS B ON A.REPAIR_ORDER_id=b.repair_order_id "
                + " inner join repair_orders as c on b.repair_order_id=c.id "
                + " INNER JOIN SUPPLIERS AS E ON B.SUPPLIER_ID=E.ID "
                + " where c.status!=-1 and price_flag!='0' " + sql
                + " group by b.supplier_id,b.repair_order_Id,E.SHORTNAME "
                + " ) as a "
                + " group by supplier_id,SHORTNAME";
            return db.Database.SqlQuery<SupplierPriceViewModel>(sqlContent);
        }

        [HttpPost]
        public JsonResult SupplierPriceSearch(DateTime? sDate, DateTime? eDate, string Supplier)
        {
            try
            {
                string sql = "";
                if (Supplier != "全部")
                    sql = " AND B.SUPPLIER_ID=" + Supplier;

                string searchSql = "";
                if (sDate != null && eDate != null)
                    searchSql = " AND CREATED_DATE>='" + sDate.Value + "' AND CREATED_DATE<'" + eDate.Value.AddDays(1) + "'";

                return Json(new { Sql = sql, SearchSql = searchSql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 厂商故障点分类汇总统计
        public ActionResult SupplierFault()
        {
            return View();
        }
        public ActionResult ListSupplierFault(DateTime? sDate, DateTime? eDate)
        {
            if (sDate == null || eDate == null)
                return Content("<Script>alert('请输入完整日期');window.location.href='/Report/SupplierFault/';</Script>");
            DataTable result = getSupplierFault(sDate, eDate);

            return excelCheckOut(result, sDate, eDate);
        }
        public DataTable getSupplierFault(DateTime? sDate, DateTime? eDate)
        {
            string sql = "";
            string searchSql = "";

            //if (Supplier != "全部")
            //    sql = " AND  B.SUPPLIER_ID=" + Supplier;
            searchSql = " AND CREATED_DATE>='" + sDate.Value + "' AND CREATED_DATE<'" + eDate.Value.AddDays(1) + "' ";

            var suppliers = db.SUPPLIERS.ToList();
            string supplierContent = "";
            foreach (var item in suppliers)
            {
                supplierContent += "COUNT(CASE WHEN B.SUPPLIER_ID=" + item.ID + " THEN 1 END )" + item.SHORTNAME + ",";
            }
            supplierContent = supplierContent.Substring(0, supplierContent.Length - 1);
            string sqlContent = "SELECT  PARTS_NAME '元器件', " + supplierContent
                + "  FROM ( "
                + "  SELECT DISTINCT REPAIR_ORDER_ID  FROM REPAIR_ORDER_TRACKING WHERE REPAIR_ORDER_STATUS IN (10,11,12,13,14) " + searchSql
                + "  )AS A INNER JOIN REPAIR_ORDER_PRICE AS B ON A.REPAIR_ORDER_id=b.repair_order_id "
                + "  inner join repair_orders as c on b.repair_order_id=c.id "
                + " INNER JOIN PARTS_TYPE AS D ON B.FAULT_PARTS=D.ID "
                + " where c.status!=-1 " + sql
                + " GROUP BY D.PARTS_NAME ";
            return new DatabaseHelper().ExecuteSqlDataSet(sqlContent).Tables[0];
        }
        /// <summary>
        /// 导出厂商故障点数据
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private ActionResult excelCheckOut(DataTable dt, DateTime? sDate, DateTime? eDate)
        {
            string exportFileTitle = "厂商故障分类统计.xls";
            string template = Server.MapPath("/Templates/厂商故障分类统计.xls");
            ExportHelper export = new ExportHelper(template);
            int startRowIndex = 3; //主要数据起始行
            int startColIndex = 0; //主要数据起始列

            //导出主要数据
            export.ExportToExcel(dt.Rows.Cast<DataRow>(), (item, excel, rowIndex) =>
            {

                int i = 0;
                for (; i < item.Table.Columns.Count; i++)
                {
                    if (i == startColIndex) //第一列写配件名
                    {
                        excel[rowIndex, i] = item[i].ToText();
                    }
                    else //其它列写数字
                    {
                        excel.SetCellValue(rowIndex, i, item[i].ToDouble());
                    }
                }


                //写入最后一列的合计信息
                Char c1 = ('A'.CharToAscii() + startColIndex + 1).AsciiToChar();
                Char c2 = ('A'.CharToAscii() + i - 1).AsciiToChar();
                string formula = string.Format("SUM({0}{1}:{2}{1})", c1, rowIndex + 1, c2);
                excel.SetCellFormula(rowIndex, i, formula);


                //设置样式
                for (int col = startColIndex; col <= i; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex, rowIndex, col);
                }

            }, 0, startRowIndex);


            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                //合并标题的单元格式
                excel.Merge(0, 0, 0, dt.Columns.Count + 1);

                //写入统计日期
                excel[1, 1] = DateTime.Now.ToString(UIConstants.DateFormatString);

                //设置表头
                int i = startColIndex + 1;
                for (; i < dt.Columns.Count; i++)
                {
                    excel[startRowIndex - 1, i] = dt.Columns[i].ColumnName;
                }

                //写表头的合计列列头
                excel[startRowIndex - 1, i] = "合计";

                //设置样式
                for (int col = startColIndex + 1; col <= i; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex + 1, startRowIndex - 1, col);
                }

                //写入日期
                excel[1, 1] = sDate.Value.ToShortDateString() + "至" + eDate.Value.ToShortDateString();

                //写入最后一行的合计信息
                excel[ep.LastRowIndex + 1, startColIndex] = "合计：";
                for (int j = startColIndex + 1; j <= dt.Columns.Count; j++)
                {
                    Char c = ('A'.CharToAscii() + j).AsciiToChar();

                    string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                    excel.SetCellFormula(ep.LastRowIndex + 1, j, formula);
                }

                //设置样式
                for (int col = startColIndex; col <= dt.Columns.Count; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex, ep.LastRowIndex + 1, col);
                }

            });

            return export.DownLoad(this.File, exportFileTitle);
        }


        #endregion

        #region 厂商维修结账统计
        public ActionResult SupplierRepairReport()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListSupplierRepair(string sql, string searchSql)
        {
            var result = getResult(sql, searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }
        /// <summary>
        /// 获取厂商维修结账结果集
        /// </summary>
        /// <returns></returns>
        public List<SupplierRepairViewModel> getResult(string sql, string searchSql)
        {
            DatabaseHelper dbH = new DatabaseHelper();
            string sqlContent = "SELECT D.AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,B.OBU_NO,B.ID,SUM(R.SUPPLIER_PRICE) PRICE  FROM (SELECT DISTINCT REPAIR_ORDER_ID FROM REPAIR_ORDER_TRACKING "
            + " WHERE REPAIR_ORDER_STATUS in (" + RepairOrderStatus.ClosedWithCustomerPickedBack.ToInt().Value + ") " + sql
            + " )AS A "
            + " INNER JOIN REPAIR_ORDERS AS B ON A.REPAIR_ORDER_ID=B.ID "
            + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
            + " INNER JOIN (SELECT AGENT_NO,AGENT_NAME,AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUER_AGENTS "
            + " UNION ALL SELECT ISSUER_NO,ISSUER_NAME,'' AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUERS) "
            + " AS D ON B.AGENT_NO=D.AGENT_NO "
            + " INNER JOIN REPAIR_ORDER_PRICE AS R ON A.REPAIR_ORDER_ID=R.REPAIR_ORDER_ID"
            + " WHERE B.STATUS not in (-1,17) AND R.PRICE_FLAG!='N' AND R.PRICE_FLAG!='0' " + searchSql
            + " AND CONFIRM_REPAIRED=" + ConfirmRepairedStatus.Repair.ToInt().Value
            + " GROUP BY  AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,B.OBU_NO,B.ID";
            DataTable dt = new DatabaseHelper().ExecuteSqlDataSet(sqlContent).Tables[0];

            string sqlOneRepair = @"SELECT 
	d.AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,B.OBU_NO,B.ID,
	sum( CONVERT(DECIMAL,BB.REMARK) ) PRICE  
FROM REPAIR_ORDERS as b
inner join OPERATION_CARE_LOGS as bb on b.id = bb.OPERATION_ID 
	INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID  
	INNER JOIN (
			SELECT AGENT_NO,AGENT_NAME,AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUER_AGENTS  
			UNION ALL SELECT ISSUER_NO,ISSUER_NAME,'' AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUERS
			)  AS D ON B.AGENT_NO=D.AGENT_NO 
	WHERE 
	B.STATUS =17 " + sql.Replace("CREATED_DATE", "RECORD_TIME") + @" AND CONFIRM_REPAIRED=1 
	GROUP BY  AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,B.OBU_NO,B.ID";
            DataTable dtOneRepair = new DatabaseHelper().ExecuteSqlDataSet(sqlOneRepair).Tables[0];
            List<SupplierRepairViewModel> result = new List<SupplierRepairViewModel>();
            foreach (DataRow item in dt.Rows)
            {
                SupplierRepairViewModel s = new SupplierRepairViewModel();
                s.agentName = item["AGENT_NAME"].ToString();
                s.shortName = item["SHORTNAME"].ToString();
                s.specName = item["SPEC_NAME"].ToString();
                s.obuNO = item["OBU_NO"].ToString();
                s.faultRemark = getFaultRemark(item["ID"].ToString(), dbH);
                s.sumPrice = Convert.ToDecimal(item["PRICE"].ToString());
                result.Add(s);
            }
            foreach (DataRow item in dtOneRepair.Rows)
            {
                SupplierRepairViewModel s = new SupplierRepairViewModel();
                s.agentName = item["AGENT_NAME"].ToString();
                s.shortName = item["SHORTNAME"].ToString();
                s.specName = item["SPEC_NAME"].ToString();
                s.obuNO = item["OBU_NO"].ToString();
                s.faultRemark = getFaultRemark(item["ID"].ToString(), dbH);
                s.sumPrice = Convert.ToDecimal(item["PRICE"].ToString());
                result.Add(s);
            }
            return result;
        }
        /// <summary>
        /// 获取维修单的故障点字符串
        /// </summary>
        /// <param name="repairOrderID"></param>
        /// <returns></returns>
        public string getFaultRemark(string repairOrderID, DatabaseHelper db)
        {
            string sqlContent = " SELECT A.REPAIR_ORDER_ID,A.SUPPLIER_PRICE,B.PARTS_NAME FROM REPAIR_ORDER_PRICE AS A "
                + " INNER JOIN PARTS_TYPE AS B ON A.FAULT_PARTS=B.ID "
                + " WHERE PRICE_FLAG!='0'  AND A.REPAIR_ORDER_ID=" + repairOrderID;
            DataTable dt = db.ExecuteSqlDataSet(sqlContent).Tables[0];
            string remark = "";
            foreach (DataRow item in dt.Rows)
            {
                remark += item["PARTS_NAME"].ToString() + "、";
            }
            return remark;
        }
        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        /// <param name="Agent"></param>
        /// <param name="Supplier"></param>
        /// <param name="ObuNO"></param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult RepairSearch(DateTime? sDate, DateTime? eDate, string Agent, string Supplier, string ObuNO, string flag)
        {
            try
            {
                string sql = "";
                string searchSql = "";
                if (eDate != null && sDate != null)
                    sql = " AND CREATED_DATE>='" + sDate.Value + "' AND CREATED_DATE<'" + eDate.Value.AddDays(1) + "' ";

                if (Agent != "全部")
                    searchSql += " AND B.AGENT_NO='" + Agent.ChangAllTextToNull().Trim() + "'";
                if (Supplier != "全部")
                    searchSql += " AND C.ID=" + Supplier.ChangAllTextToNull();
                if (ObuNO.IsNullOrEmpty() == false)
                    searchSql += " AND B.OBU_NO='" + ObuNO + "'";

                if (flag == "report")
                    return ReportRepair(sql, searchSql, sDate, eDate);
                else
                    return Json(new { Sql = sql, SearchSql = searchSql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                if (flag == "report")
                    return Content("<script>alert('导出失败！');</script>");
                return Json(new { Sql = "", Success = false, ErrorMsg = ex.Message });
            }
        }

        private ActionResult ReportRepair(string sql, string searchSql, DateTime? sDate, DateTime? eDate)
        {
            //厂商维修结账统计.xls
            List<SupplierRepairViewModel> list = getResult(sql, searchSql);
            if (list.Count > 0)
            {
                string exportFileTitle = "厂商维修结账统计.xls";
                string template = Server.MapPath("/Templates/厂商维修结账统计.xls");
                ExportHelper export = new ExportHelper(template);
                string dateStr = "全部时间";
                if (sDate != null && eDate != null)
                {
                    dateStr = sDate.Value.ToShortDateString() + " 至 " + eDate.Value.ToShortDateString();
                }
                ExportRepair(export, list, dateStr);
                return export.DownLoad(this.File, exportFileTitle);
            }
            else
            {
                return Content("<script>alert('该条件范围内数据不存在！');window.location.href='/Report/SupplierRepairReport/';</script>");
            }
        }

        private void ExportRepair(ExportHelper export, List<SupplierRepairViewModel> list, string dateStr)
        {
            int startRowIndex = 3;//主要数据起始行
            //int startCellIndex = 0;//主要数据起始列
            int sheetIndex = 0;

            if (list.Count > 0)
            {
                export.ExportToExcel(list, (item, excel, rowIndex) =>
                {
                    excel[rowIndex, 0] = item.agentName;
                    excel[rowIndex, 1] = item.shortName;
                    excel[rowIndex, 2] = item.specName;
                    excel[rowIndex, 3] = item.obuNO;
                    excel[rowIndex, 4] = item.faultRemark;
                    excel.SetCellValue(rowIndex, 5, item.sumPrice);
                    for (int col = startRowIndex + 1; col < list.Count + startRowIndex; col++)
                    {
                        excel.SetCellStyle(startRowIndex, 0, col, 0);
                        excel.SetCellStyle(startRowIndex, 1, col, 1);
                        excel.SetCellStyle(startRowIndex, 2, col, 2);
                        excel.SetCellStyle(startRowIndex, 3, col, 3);
                        excel.SetCellStyle(startRowIndex, 4, col, 4);
                        excel.SetCellStyle(startRowIndex, 5, col, 5);
                    }
                }, sheetIndex, startRowIndex);

                export.ExportAddionalInfomation((excel, ep) =>
                {
                    excel[1, 0] = "日期：" + dateStr;
                    excel[ep.LastRowIndex + 1, 0] = "合计：";

                    Char c = ('A'.CharToAscii() + 5).AsciiToChar();

                    string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                    excel.SetCellFormula(ep.LastRowIndex + 1, 5, formula);

                    for (int col = 0; col <= 5; col++)
                    {
                        excel.SetCellStyle(startRowIndex, col, ep.LastRowIndex + 1, col);
                    }
                });
            }
        }
        #endregion

        #region 维护维修日报汇总
        public ActionResult RepairDaily()
        {
            //var result = getResult(searchSql);
            //decimal returnDeposit = 0, collectsDeposit = 0, repairObu = 0, issueSpareTape = 0, issueBase = 0, repairServiceFee = 0, sumPrice = 0;
            //if (result != null && result.Count() > 0)
            //{
            //    foreach (var item in result)
            //    {
            //        returnDeposit += item.ReturnDeposit;
            //        collectsDeposit += item.CollectsDeposit;
            //        repairObu += item.RepairObu;
            //        issueSpareTape += item.IssueSpareTape;
            //        issueBase += item.IssueBase;
            //        repairServiceFee += item.RepairServiceFee;
            //        sumPrice += item.SUM_PRICE;
            //    }
            //}
            //ViewBag.ReturnDeposit = returnDeposit;
            //ViewBag.CollectsDeposit = collectsDeposit;
            //ViewBag.RepairObu = repairObu;
            //ViewBag.IssueSpareTape = issueSpareTape;
            //ViewBag.IssueBase = issueBase;
            //ViewBag.RepairServiceFee = repairServiceFee;
            //ViewBag.SUM_PRICE = sumPrice;
            return View();
        }
        [GridAction]
        public ActionResult ListRepairDaily(string searchSql)
        {
            var result = getResult(searchSql, false);
            var temp = new GridModel(result);
            return View("RepairDaily", temp);
        }
        public ActionResult _viewSum()
        {
            ViewBag.ReturnDeposit = 0;
            return ViewBag.ReturnDeposit;
        }
        public IEnumerable<RepairDailyViewModel> getResult(string searchSql, bool isSelectSum)
        {
            var dictResult = new ConfigMgr().GetTypeValues(ConfigMgr.tblTransactionType);
            string content = "";
            foreach (var item in dictResult)
            {
                content += "ISNULL(SUM(CASE WHEN OPERATION_KIND='" + item.DICT_CODE + "' THEN AMOUNT END),0)" + Enum.Parse(typeof(TransactionType), item.DICT_CODE).ToText() + ",";
            }
            content = content.Substring(0, content.Length - 1);
            string sqlContent = " SELECT " + (isSelectSum ? "" : "CONVERT(DATE,RECORD_TIME) RECORD_DATE ,I.AGENT_NO,I.AGENT_NAME,O.OPERATOR_NO,O.OPERATOR_NAME,") + content
                + " FROM OPERATION_CARE_LOGS  AS A "
                + " INNER JOIN PRINT_DETAIL AS B ON A.ID=B.LOG_ID "
                + " INNER JOIN PRINT_LOG AS C ON C.ID=B.SERIAL_ID "
                + " INNER JOIN ISSUE_RPT.DBO.ISSUER_AGENTS AS I ON C.AGENT_NO=I.AGENT_NO "
                + " INNER JOIN ISSUE_RPT.DBO.OPERATORS AS O ON C.OPERATOR_NO=O.OPERATOR_NO " + searchSql + (searchSql.ToUpper().Contains("WHERE") ? " AND A.STATUS=1 AND C.STATUS=1 " : " WHERE A.STATUS=1 AND C.STATUS=1 ")
                + (isSelectSum ? "" : " GROUP BY CONVERT(DATE,RECORD_TIME), I.AGENT_NO,I.AGENT_NAME,O.OPERATOR_NO,O.OPERATOR_NAME ");
            return db.Database.SqlQuery<RepairDailyViewModel>(sqlContent);
        }

        [HttpPost]
        public ActionResult repairDailySearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Agent, string Operator) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                var searchSql = obuMgr.GetRepairDailySearch(FromCreatedDate, ToCreatedDate, Agent.ChangAllTextToNull(), Operator.ChangAllTextToNull());
                var result = getResult("WHERE " + searchSql, true).ToList()[0];
                decimal oneRepair = 0, returnDeposit = 0, collectsDeposit = 0, repairObu = 0, issueSpareTape = 0, issueBase = 0, repairServiceFee = 0, sumPrice = 0;
                oneRepair += result.OneRepair;
                returnDeposit += result.ReturnDeposit;
                collectsDeposit += result.CollectsDeposit;
                repairObu += result.RepairObu;
                issueSpareTape += result.IssueSpareTape;
                issueBase += result.IssueBase;
                repairServiceFee += result.RepairServiceFee;
                sumPrice += result.SUM_PRICE;

                return Json(new { SearchSql = "WHERE " + searchSql, Success = true, ErrorMsg = "", OneRepair = oneRepair, ReturnDeposit = returnDeposit, CollectsDeposit = collectsDeposit, RepairObu = repairObu, IssueSpareTape = issueSpareTape, IssueBase = issueBase, RepairServiceFee = repairServiceFee, SUM_PRICE = sumPrice });
            }
            catch (Exception ex)
            {
                return Json(new { SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 厂商维修周期统计
        public ActionResult SupplierRepairTime()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListRepairTime(string searchSql)
        {
            var result = getSupplierRepairTime(searchSql);
            var temp = new GridModel(result);
            return View(temp);
        }
        public IEnumerable<RepairTimeViewModel> getSupplierRepairTime(string searchSql)
        {
            try
            {
                searchSql = searchSql == "" ? "" : " WHERE " + searchSql;
                string sqlContent = "SELECT C.SHORTNAME,COUNT(DISTINCT REPAIR_ORDER_ID) SUM_ORDER,"
                    + " SUM(DATEDIFF ( day  ,B.CREATED_DATE, A.CREATED_DATE  ))/ COUNT(DISTINCT REPAIR_ORDER_ID) AVG_DAY "
                    + " FROM (SELECT DISTINCT REPAIR_ORDER_ID,MAX(CREATED_DATE) CREATED_DATE FROM REPAIR_ORDER_TRACKING " + searchSql
                    + " GROUP BY REPAIR_ORDER_ID) AS A  "
                    + " INNER JOIN REPAIR_ORDERS AS B ON A.REPAIR_ORDER_ID=B.ID "
                    + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
                    + " WHERE B.STATUS!=-1 "
                    + " GROUP BY SHORTNAME ";
                return db.Database.SqlQuery<RepairTimeViewModel>(sqlContent);
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public ActionResult RepairTimeSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Status)
        {
            try
            {
                string sql = "";
                if (FromCreatedDate != null && ToCreatedDate != null)
                    sql = " CREATED_DATE>='" + FromCreatedDate.Value + "' AND CREATED_DATE<'" + ToCreatedDate.Value.AddDays(1) + "' ";
                if (Status != "全部")
                    sql += " AND REPAIR_ORDER_STATUS=" + Status;

                return Json(new { SearchSql = sql, Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 厂商维修周期明细查询
        public ActionResult RepairTimeDetail()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListRepairTimeDetail(string sql, string searchSql, string Check_Day)
        {
            var result = getRepairDetail(sql, searchSql, Check_Day);
            var temp = new GridModel(result);
            return View(temp);
        }
        public IEnumerable<RepairTimeDetailModel> getRepairDetail(string sql, string searchSql, string Check_Day)
        {
            string sqlContent = " SELECT D.AGENT_NAME,B.OBU_NO,C.SHORTNAME,E.DICT_TITLE AS CURRENT_STATUS,B.CREATED_DATE,"
            + " DATEDIFF ( day  ,B.CREATED_DATE, A.CREATED_DATE  )+1-" + Check_Day + " REPAIR_DAYS "
            + " FROM ( SELECT DISTINCT REPAIR_ORDER_ID,MAX(CREATED_DATE) CREATED_DATE FROM REPAIR_ORDER_TRACKING " + sql
            + " GROUP BY REPAIR_ORDER_ID) AS A "
            + " INNER JOIN REPAIR_ORDERS AS B ON A.REPAIR_ORDER_ID=B.ID "
            + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
            + " INNER JOIN (SELECT AGENT_NO,AGENT_NAME,AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUER_AGENTS  "
            + " UNION ALL SELECT ISSUER_NO,ISSUER_NAME,'' AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUERS) AS D ON B.AGENT_NO=D.AGENT_NO "
            + " INNER JOIN (SELECT B.* FROM DICT_TYPE AS A INNER JOIN DICT_TYPE_VALUE AS B ON B.DICT_TYPE_ID=A.ID "
            + " WHERE A.TYPE_CODE='" + ConfigMgr.tblRepairOrderStatus + "') AS E ON B.STATUS=E.DICT_CODE "
            + " WHERE B.STATUS!=-1 " + searchSql;
            return db.Database.SqlQuery<RepairTimeDetailModel>(sqlContent);
        }
        [HttpPost]
        public ActionResult RepairTimeDetailSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Status,
            string Supplier, string Agent, string txt_day, string txt_obuNo) //也可以用这种格式接收参数public ActionResult Search(FormCollection form)
        {
            try
            {
                string sql = "";
                string searchSql = "";
                if (FromCreatedDate != null && ToCreatedDate != null)
                    sql = " CREATED_DATE >='" + FromCreatedDate.Value + "' AND CREATED_DATE<'" + ToCreatedDate.Value.AddDays(1) + "'";
                if (Status != "全部")
                    sql += " AND REPAIR_ORDER_STATUS=" + Status;
                if (Supplier != "全部")
                    searchSql += " AND B.SUPPLIER_ID='" + Supplier + "'";
                if (Agent != "全部")
                    searchSql += " AND B.AGENT_NO='" + Agent + "'";
                if (txt_obuNo.Trim() != "")
                    searchSql += " AND B.OBU_NO ='" + txt_obuNo + "'";

                sql = sql == "" ? "" : " WHERE " + sql;
                return Json(new { Sql = sql, SearchSql = searchSql, Check_Day = txt_day.ChangAllTextToNull().Trim(), Success = true, ErrorMsg = "" });
            }
            catch (Exception ex)
            {
                return Json(new { Sql = "", SearchSql = "", Check_Day = 0, Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 元器件使用情况日汇总
        public ActionResult PartsSaleCol()
        {
            return View();
        }
        [GridAction]
        public ActionResult ListPartSaleCol(string searchSql)
        {
            var result = getPartSaleInfo(searchSql, false);
            var temp = new GridModel(result);
            return View(temp);
        }
        public IEnumerable<PartSaleCollect> getPartSaleInfo(string searchSql, bool isGetSum)
        {
            try
            {
                searchSql = searchSql == "" ? "" : " WHERE " + searchSql;
                string sqlContent = " SELECT " + (isGetSum ? "" : "D.AGENT_NAME,C.SHORTNAME,B.SPEC_NAME,")
                    + " COUNT(CASE WHEN A.PARTS_ID=16 AND AMOUNT>0 THEN 1 END) 'BJ_SALE_COUNT',"
                    + " ISNULL(SUM(CASE WHEN A.PARTS_ID=16 AND AMOUNT>0 THEN AMOUNT END),0) 'BJ_SALE', "
                    + " COUNT(CASE WHEN A.PARTS_ID=17 AND AMOUNT>0 THEN 1 END) 'DZ_SALE_COUNT',"
                    + " ISNULL(SUM(CASE WHEN A.PARTS_ID=17 AND AMOUNT>0 THEN AMOUNT END),0) 'DZ_SALE',"
                    + " COUNT(CASE WHEN A.PARTS_ID=16 AND AMOUNT=0 THEN 1 END) 'BJ_FREE',"
                    + " COUNT(CASE WHEN A.PARTS_ID=17 AND AMOUNT=0 THEN 1 END) 'DZ_FREE' "
                    + " FROM PARTS_SALE AS A "
                    + " INNER JOIN PARTS_SPECS AS B ON A.PARTS_SPEC_ID=B.ID "
                    + " INNER JOIN SUPPLIERS AS C ON B.SUPPLIER_ID=C.ID "
                    + " INNER JOIN ISSUE_RPT.DBO.ISSUER_AGENTS AS D ON A.AGENT_NO=D.AGENT_NO " + searchSql + (searchSql.ToUpper().Contains("WHERE") ? " AND A.STATUS!=-1 " : " WHERE A.STATUS!=-1 ")
                    + (isGetSum ? "" : " GROUP BY D.AGENT_NAME,C.SHORTNAME,B.SPEC_NAME ");
                return db.Database.SqlQuery<PartSaleCollect>(sqlContent);
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public ActionResult PartSaleSearch(DateTime? FromCreatedDate, DateTime? ToCreatedDate, string Agent, string Supplier)
        {
            try
            {

                List<string> querys = new List<string>();
                if (FromCreatedDate.HasValue)
                {
                    querys.Add(string.Format(" [REPORT_DATE]>='{0}' ", FromCreatedDate.Value.ToString("yyyy-MM-dd")));
                }

                if (ToCreatedDate.HasValue)
                {
                    querys.Add(string.Format(" [REPORT_DATE]<'{0}' ", ToCreatedDate.Value.AddDays(1).ToString("yyyy-MM-dd")));
                }

                if (Agent != "全部")
                {
                    querys.Add(string.Format(" A.[AGENT_NO]='{0}' ", Agent));
                }

                if (Supplier != "全部")
                {
                    querys.Add(string.Format(" A.SUPPLIER_ID='{0}' ", Supplier));
                }

                string searchSql = querys.ConcatToString(" and ");
                List<PartSaleCollect> result = getPartSaleInfo(searchSql, true).ToList();

                int bjSaleCount = 0, dzSaleCount = 0;
                decimal bjSale = 0, dzSale = 0, bjFree = 0, dzFree = 0;
                bjSaleCount += result[0].BJ_SALE_COUNT;
                dzSaleCount += result[0].DZ_SALE_COUNT;
                bjSale += result[0].BJ_SALE;
                dzSale += result[0].DZ_SALE;
                bjFree += result[0].BJ_FREE;
                dzFree += result[0].DZ_FREE;
                return Json(new { SearchSql = searchSql, Success = true, ErrorMsg = "", BJ_SALE_COUNT = bjSaleCount, DZ_SALE_COUNT = dzSaleCount, BJ_SALE = bjSale, DZ_SALE = dzSale, BJ_FREE = bjFree, DZ_FREE = dzFree });
            }
            catch (Exception ex)
            {

                return Json(new { SearchSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        #endregion

        #region 元器件库存统计

        public ActionResult PartsStockCol()
        {
            return View();
        }
        public ActionResult ListPartsStock(DateTime? sDate, string AgentType, string Agent)
        {
            if (sDate == null)
                return Content("<Script>alert('请输入完整日期');window.location.href='/Report/PartsStockCol/';</Script>");
            List<string> querys = new List<string>();
            if (sDate.HasValue)
            {
                querys.Add(string.Format(" [SUM_DATE]='{0}' ", sDate.Value.ToString("yyyy-MM-dd")));
            }

            if (AgentType != "全部")
            {
                querys.Add(string.Format(" B.[AGENT_TYPE_NO]='{0}' ", AgentType));
            }

            if (Agent != "全部")
            {
                querys.Add(string.Format(" A.[AGENT_NO]='{0}' ", Agent));
            }

            string searchSql = querys.ConcatToString(" and ");

            string exportFileTitle = "元器件库存统计.xls";
            string template = Server.MapPath("/Templates/元器件库存统计.xls");
            ExportHelper export = new ExportHelper(template);

            searchSql = searchSql == "" ? "" : " AND " + searchSql;

            DataTable dtBJ = getStockCollect(searchSql, 16);
            DataTable dtDZ = getStockCollect(searchSql, 17);
            string agentName = "";
            if (dtBJ.Rows.Count <= 0 || dtDZ.Rows.Count <= 0)
            {
                agentName = db.Database.SqlQuery<string>("SELECT AGENT_NAME FROM ISSUE_RPT.DBO.ISSUER_AGENTS WHERE AGENT_NO='" + Agent + "'  UNION ALL SELECT ISSUER_NAME FROM ISSUE_RPT.DBO.ISSUERS  WHERE ISSUER_NO='" + Agent + "';").FirstOrDefault();
            }

            if (dtBJ.Rows.Count <= 0)
            {
                DataRow dr = dtBJ.NewRow();
                dr[0] = agentName;
                for (int i = 1; i < dtBJ.Columns.Count; i++)
                {
                    dr[i] = 0;
                }
                dtBJ.Rows.Add(dr);
            }
            if (dtDZ.Rows.Count <= 0)
            {
                DataRow dr = dtDZ.NewRow();
                dr[0] = agentName;
                for (int i = 1; i < dtDZ.Columns.Count; i++)
                {
                    dr[i] = 0;
                }
                dtDZ.Rows.Add(dr);
            }
            exportStock(export, dtBJ, 0, sDate.Value.ToShortDateString());
            exportStock(export, dtDZ, 1, sDate.Value.ToShortDateString());

            return export.DownLoad(this.File, exportFileTitle);
        }
        public DataTable getStockCollect(string searchSql, int partID)
        {
            try
            {
                DataTable dt = new DataTable();
                var partsSpecs = (from i in db.PARTS_SPECS.ToList()
                                  where i.PARTS_ID == partID && i.STATUS == DictTypeStatus.Normal.ToInt().Value
                                  select i).ToList();
                if (partsSpecs != null && partsSpecs.Count() > 0)
                {
                    string supplierContent = "";
                    foreach (var item in partsSpecs)
                    {
                        supplierContent += " ISNULL( SUM(CASE WHEN PARTS_SPEC_ID=" + item.ID + " THEN CURRENT_STOCK END) ,0) '" + item.SPEC_NAME + "',";
                    }
                    supplierContent = supplierContent.Substring(0, supplierContent.Length - 1);
                    string sqlContent = "SELECT B.AGENT_NAME," + supplierContent
                        + " FROM SUM_PARTS_STOCK  AS A "
                        + " INNER JOIN (SELECT AGENT_NO,AGENT_NAME,AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUER_AGENTS "
                        + " UNION ALL SELECT ISSUER_NO,ISSUER_NAME,''AGENT_TYPE_NO FROM ISSUE_RPT.DBO.ISSUERS) "
                        + " AS B ON A.AGENT_NO=B.AGENT_NO  WHERE PARTS_ID=" + partID + searchSql
                        + "  GROUP BY B.AGENT_NAME ORDER BY  B.AGENT_NAME";
                    dt = new DatabaseHelper().ExecuteSqlDataSet(sqlContent).Tables[0];
                }
                return dt;
            }
            catch (Exception ex)
            {

                throw;
            }

        }

        /// <summary>
        /// 导出库存统计数
        /// </summary>
        /// <param name="export"></param>
        /// <param name="sql"></param>
        /// <param name="searchSql"></param>
        /// <param name="FromCreatedDate"></param>
        /// <param name="ToCreatedDate"></param>
        private void exportStock(ExportHelper export, DataTable dt, int sheetIndex, string sDate)
        {
            int startRowIndex = 3; //主要数据起始行
            int startColIndex = 0; //主要数据起始列

            int printDateIndex = sheetIndex == 0 ? 9 : 11;

            //将合计行往后移动数据总行数一1
            export.ExportAddionalInfomation((excel, ep) =>
            {
                excel.ActiveSheetIndex = sheetIndex;
                excel.ShiftRows(4, 5, dt.Rows.Count - 1);
            });

            if (dt.Rows.Count > 0)
            {
                //导出主要数据
                export.ExportToExcel(dt.Rows.Cast<DataRow>(), (item, excel, rowIndex) =>
                {
                    int i = 0;
                    for (; i < item.Table.Columns.Count; i++)
                    {
                        if (i == startColIndex) //第一列写配件名
                        {
                            excel[rowIndex, i] = item[i].ToText();
                        }
                        else //其它列写数字
                        {
                            excel.SetCellValue(rowIndex, i, item[i].ToDouble());
                        }
                    }

                    //写入最后一列的合计信息
                    Char c1 = ('A'.CharToAscii() + startColIndex + 1).AsciiToChar();
                    Char c2 = ('A'.CharToAscii() + i - 1).AsciiToChar();
                    string formula = string.Format("SUM({0}{1}:{2}{1})", c1, rowIndex + 1, c2);
                    excel.SetCellFormula(rowIndex, i, formula);


                    //设置样式
                    for (int col = startColIndex; col <= i; col++)
                    {
                        excel.SetCellStyle(startRowIndex, startColIndex, rowIndex, col);
                    }


                }, sheetIndex, startRowIndex);

            }


            //导出合计，日期等其它的信息
            export.ExportAddionalInfomation((excel, ep) =>
            {
                excel.ActiveSheetIndex = sheetIndex;
                //合并标题的单元格式
                excel.Merge(0, 0, 0, dt.Columns.Count + 1);

                //写入统计日期
                excel[1, 1] = sDate;

                //设置表头
                int i = startColIndex + 1;
                for (; i < dt.Columns.Count; i++)
                {
                    excel[startRowIndex - 1, i] = dt.Columns[i].ColumnName;
                }

                if (dt != null && dt.Rows.Count > 0)
                {
                    //写表头的合计列列头
                    excel[startRowIndex - 1, i] = "合计";

                    //设置样式
                    for (int col = startColIndex + 1; col <= i; col++)
                    {
                        excel.SetCellStyle(startRowIndex, startColIndex + 1, startRowIndex - 1, col);
                    }

                    //写入日期

                    //写入最后一行的合计信息
                    excel[ep.LastRowIndex + 1, startColIndex] = "合计：";

                    for (int j = startColIndex + 1; j <= dt.Columns.Count; j++)
                    {
                        Char c = ('A'.CharToAscii() + j).AsciiToChar();

                        string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                        excel.SetCellFormula(ep.LastRowIndex + 1, j, formula);
                    }
                }

                //设置样式
                for (int col = startColIndex; col <= dt.Columns.Count; col++)
                {
                    excel.SetCellStyle(startRowIndex, startColIndex, ep.LastRowIndex + 1, col);
                }

            });
        }

        #endregion

        #region 电子标签更换统计
        public ActionResult ObuChangeStatistics()
        {
            return View();
        }

        [GridAction]
        public ActionResult ListObuChange(string dateSql, string otherSql)
        {
            var result = GetObuChange(dateSql, otherSql);
            var temp = new GridModel(result);
            return View("RepairDaily", temp);
        }

        private List<ObuChangeStatisticsViewModel> GetObuChange(string dateSql, string otherSql)
        {
            try
            {
                string sql = "SELECT "
                    + " CASE WHEN  S.SHORTNAME IS NULL THEN '其它' ELSE S.SHORTNAME END '厂商' ,"
                    + "CASE WHEN D.SPEC_CODE IS NULL THEN '其它' ELSE D.SPEC_CODE END '型号',"
                    + "COUNT(CASE WHEN CHANGE_COUNT=1 THEN 1 END)'更换一次',"
                    + "COUNT(CASE WHEN CHANGE_COUNT=2 THEN 1 END)'更换两次',"
                    + "COUNT(CASE WHEN CHANGE_COUNT=3 THEN 1 END)'更换三次',"
                    + "COUNT(CASE WHEN CHANGE_COUNT>=4 THEN 1 END)'更换四次以上'FROM ("
                    + "SELECT LAST_OBU_NO,COUNT(1) CHANGE_COUNT "
                    + "FROM ISSUE_RPT.DBO.OBU_ISSUE "
                    + "WHERE LAST_OBU_NO IS NOT NULL AND LAST_OBU_NO!='' " + (string.IsNullOrEmpty(dateSql) ? "" : " AND " + dateSql)
                    + " GROUP BY LAST_OBU_NO ) AS A "
                    + "LEFT JOIN CONTRACT_DETAIL AS B ON A.LAST_OBU_NO BETWEEN B.START_NO AND B.END_NO AND B.STATUS!=-1 "
                    + "LEFT JOIN CONTRACTS AS CON ON B.CONTRACT_ID=CON.ID "
                    + "LEFT JOIN SUPPLIERS AS S ON CON.SECOND_PARTY=S.ID "
                    + "LEFT JOIN SPECS AS C ON B.VERSION_ID=C.ID "
                    + "LEFT JOIN DEVICE_VERSION AS D ON C.VERSION_ID=D.ID "
                    + otherSql
                    + " GROUP BY SHORTNAME,SPEC_CODE ORDER BY SHORTNAME,SPEC_CODE";
                return db.Database.SqlQuery<ObuChangeStatisticsViewModel>(sql).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        [HttpPost]
        public ActionResult ObuChangeSearch(DateTime? sDate, DateTime? eDate, string Supplier, string flag)
        {
            try
            {
                string dateSql = "", otherSql = "", dateStr = "全部时间";
                if (sDate != null && eDate != null)
                {
                    dateSql = " REGISTE_DATE>='" + sDate.Value + "' AND REGISTE_DATE<'" + eDate.Value.AddDays(1) + "' ";
                    dateStr = sDate.Value.ToShortDateString() + "至" + eDate.Value.ToShortDateString();
                }
                if (Supplier != "全部")
                    otherSql += " WHERE S.ID=" + Supplier;
                if (flag == "report")
                    return ExcelObuChange(dateStr, dateSql, otherSql);
                else
                    return Content(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(new { DateSql = dateSql, OtherSql = otherSql, Success = true, ErrorMsg = "" }));
            }
            catch (Exception ex)
            {
                if (flag == "report")
                    return Content("<script>alert('导出失败！');</script>");
                return Json(new { DateSql = "", OtherSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }

        private ActionResult ExcelObuChange(string dateStr, string dateSql, string otherSql)
        {
            List<ObuChangeStatisticsViewModel> list = GetObuChange(dateSql, otherSql);
            if (list.Count > 0)
            {
                string exportFileTitle = "电子标签更换次数统计.xls";
                string template = Server.MapPath("/Templates/电子标签更换次数统计.xls");
                ExportHelper export = new ExportHelper(template);
                ExportObuChange(export, list, dateStr);
                return export.DownLoad(this.File, exportFileTitle);
            }
            else
            {
                return Content("<script>alert('该条件范围内数据不存在！');window.location.href='/Report/ObuChangeStatistics/';</script>");
            }
        }

        private void ExportObuChange(ExportHelper export, List<ObuChangeStatisticsViewModel> list, string sDate)
        {
            int startRowIndex = 3;//主要数据起始行
            //int startCellIndex = 0;//主要数据起始列
            int sheetIndex = 0;

            if (list.Count > 0)
            {
                export.ExportToExcel(list, (item, excel, rowIndex) =>
                {
                    excel[rowIndex, 0] = item.厂商;
                    excel[rowIndex, 1] = item.型号;
                    excel.SetCellValue(rowIndex, 2, item.更换一次);
                    excel.SetCellValue(rowIndex, 3, item.更换两次);
                    excel.SetCellValue(rowIndex, 4, item.更换三次);
                    excel.SetCellValue(rowIndex, 5, item.更换四次以上);
                    for (int col = startRowIndex + 1; col < list.Count + startRowIndex; col++)
                    {
                        excel.SetCellStyle(startRowIndex, 0, col, 0);
                        excel.SetCellStyle(startRowIndex, 1, col, 1);
                        excel.SetCellStyle(startRowIndex, 2, col, 2);
                        excel.SetCellStyle(startRowIndex, 3, col, 3);
                        excel.SetCellStyle(startRowIndex, 4, col, 4);
                        excel.SetCellStyle(startRowIndex, 5, col, 5);
                    }
                }, sheetIndex, startRowIndex);

                export.ExportAddionalInfomation((excel, ep) =>
                {
                    //制表人
                    var loginer = UIUtility.GetCurrentUser();
                    excel[1, 3] = "日期：" + sDate;
                    excel[ep.LastRowIndex + 1, 0] = "合计：";

                    for (int j = 2; j <= 5; j++)
                    {
                        Char c = ('A'.CharToAscii() + j).AsciiToChar();

                        string formula = string.Format("SUM({0}{1}:{0}{2})", c, startRowIndex + 1, ep.LastRowIndex + 1);
                        excel.SetCellFormula(ep.LastRowIndex + 1, j, formula);
                    }
                    for (int col = 0; col <= 5; col++)
                    {
                        excel.SetCellStyle(startRowIndex, col, ep.LastRowIndex + 1, col);
                    }
                });
            }
        }

        #endregion

        #region 电子标签历史周期信息查询
        public ActionResult ObuHistoryInfo()
        {
            return View();
        }

        public ActionResult ObuHistoryInfoSearch(string txtObuNo, string txtCustomerNo, string txtCarNo, DateTime? sDate, DateTime? eDate)
        {
            try
            {
                string dateSql = "", obuNoSql = "", userSql = "", carSql = "";
                if (sDate != null && eDate != null)
                {
                    dateSql = " AND RECORD_TIME>='" + sDate.Value + "' AND RECORD_TIME<'" + eDate.Value.AddDays(1) + "' ";
                }
                if (!string.IsNullOrEmpty(txtObuNo))
                    obuNoSql = txtObuNo;
                if (!string.IsNullOrEmpty(txtCustomerNo))
                    userSql = " AND C.USER_NO='" + txtCustomerNo + "' ";
                if (!string.IsNullOrEmpty(txtCarNo))
                    carSql = " AND B.VEHICLE_PLATE_NO='" + txtCarNo + "' ";
                return Content(new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(new { DateSql = dateSql, ObuNoSql = obuNoSql, UserSql = userSql, CarSql = carSql, Success = true, ErrorMsg = "" }));
            }
            catch (Exception ex)
            {
                return Json(new { DateSql = "", ObuNoSql = "", UserSql = "", CarSql = "", Success = false, ErrorMsg = ex.Message });
            }
        }
        [GridAction]
        public ActionResult ListObuHistoryInfo(string dateSql, string obuNoSql, string userSql, string carSql)
        {
            var result = GetObuHistoryInfo(dateSql, obuNoSql, userSql, carSql);
            var temp = new GridModel(result.AsEnumerable<View_ObuHistoryInfo>());
            return View("ObuHistoryInfo", temp);
        }

        private List<View_ObuHistoryInfo> GetObuHistoryInfo(string dateSql, string obuNoSql, string userSql, string carSql)
        {
            List<string> oldObuNos = GetAllHistoryObuNoByDate(obuNoSql, dateSql);
            List<View_ObuHistoryInfo> list = new List<View_ObuHistoryInfo>();
            oldObuNos = oldObuNos.Distinct().ToList();
            foreach (var item in oldObuNos)
            {
                //string sql = "SELECT ROW_NUMBER() over(order by RECORD_TIME) as ID,A.REMARK AS ObuNo,B.USER_NO as UserNo,C.USER_NAME as UserName,B.VEHICLE_PLATE_NO as CarNo,(case a.OPERATION_KIND when 162 then '标签发行' when 168 then '标签过户' when 160 then '标签正常更换' when 161 then '标签损坏更换' when 165 then '标签注销' when 187 then '标签以旧换新发行' when 175 then '标签以旧换新正常更换' when 178 then '标签以旧换新损坏更换' when 179 then '标签以旧换新正常回收' when 180 then '标签以旧换新损坏回收' when 188 then '标签以旧换翻新发行' when 189 then '标签以旧换翻新更换' when 190 then '标签以旧换翻新损坏更换' when 191 then '标签以旧换翻新回收' when 195 then '标签以旧换翻新损坏回收' when 196 then '标签重构升级' end) as OperationType,A.CARD_ID AS ObuNoOld,RECORD_TIME as OperationTime,E.AGENT_NAME AS AgentName,O.OPERATOR_NAME as Operator FROM (select * FROM ISSUE_RPT..OPERATION_LOGS WHERE REMARK='" + obuNoSql + "' AND OPERATION_KIND IN (162,168,160,161,165,187,175,178,179,180,188,189,190,191,195,196) " + dateSql + ") AS A INNER JOIN ISSUE_RPT..OBU_ISSUE AS B ON A.CARD_ID=B.OBU_NO  AND ((CONVERT(DATE,A.RECORD_TIME)>=CONVERT(DATE,B.REGISTE_DATE) AND B.OBU_STATE=3) OR (CONVERT(DATE,A.RECORD_TIME)<=CONVERT(DATE,B.MODIFY_DATE) AND B.OBU_STATE!=3)) " + carSql + " INNER JOIN ISSUE_RPT..CUSTOMERS AS C ON B.USER_NO=C.USER_NO " + userSql + " INNER JOIN ISSUE_RPT..TABLE_POSINFO AS D ON A.POS_ID=D.ID INNER JOIN (SELECT AGENT_NO,AGENT_NAME FROM ISSUE_RPT..ISSUER_AGENTS UNION ALL SELECT ISSUER_NO,ISSUER_NAME FROM ISSUE_RPT..ISSUERS) AS E ON D.AGENT_NO=E.AGENT_NO INNER JOIN ISSUE_RPT..OPERATORS AS O ON A.ADMID=O.OPERATOR_NO";
                string sql = @"
	select row_number() over (order by operationTime desc) as ID,* from (
	SELECT A.REMARK AS ObuNo,B.USER_NO as UserNo,C.USER_NAME as UserName,B.VEHICLE_PLATE_NO as CarNo,(case a.OPERATION_KIND when 162 then '标签发行' when 168 then '标签过户' when 160 then '标签正常更换' when 161 then '标签损坏更换' when 165 then '标签注销' when 187 then '标签以旧换新发行' when 175 then '标签以旧换新正常更换' when 178 then '标签以旧换新损坏更换' when 179 then '标签以旧换新正常回收' when 180 then '标签以旧换新损坏回收' when 188 then '标签以旧换翻新发行' when 189 then '标签以旧换翻新更换' when 190 then '标签以旧换翻新损坏更换' when 191 then '标签以旧换翻新回收' when 195 then '标签以旧换翻新损坏回收' when 196 then '标签重构升级' end) as OperationType,A.CARD_ID AS ObuNoOld,RECORD_TIME as OperationTime,E.AGENT_NAME AS AgentName,O.OPERATOR_NAME as Operator FROM (select * FROM ISSUE_RPT..OPERATION_LOGS WHERE REMARK='" + item + "' AND OPERATION_KIND IN (162,168,160,161,165,187,175,178,179,180,188,189,190,191,195,196) " + dateSql + @" ) AS A INNER JOIN ISSUE_RPT..OBU_ISSUE AS B ON A.CARD_ID=B.OBU_NO  AND ((CONVERT(DATE,A.RECORD_TIME)>=CONVERT(DATE,B.REGISTE_DATE) AND B.OBU_STATE=3) OR (CONVERT(DATE,A.RECORD_TIME)<=CONVERT(DATE,B.MODIFY_DATE) AND B.OBU_STATE!=3)) " + carSql + "  INNER JOIN ISSUE_RPT..CUSTOMERS AS C ON B.USER_NO=C.USER_NO " + userSql + @"  INNER JOIN ISSUE_RPT..TABLE_POSINFO AS D ON A.POS_ID=D.ID left JOIN (SELECT AGENT_NO,AGENT_NAME FROM ISSUE_RPT..ISSUER_AGENTS UNION ALL SELECT ISSUER_NO,ISSUER_NAME FROM ISSUE_RPT..ISSUERS) AS E ON D.AGENT_NO=E.AGENT_NO left JOIN ISSUE_RPT..OPERATORS AS O ON A.ADMID=O.OPERATOR_NO
	    union all
	    SELECT 
	    OBU_NO  obuNo,
	    USER_NO userNo,
	    OWNER_NAME userName,
	VEHICLE_PLATE_NO carNo,
	(CASE WHEN ORDER_TYPE=2 THEN '一口价维修' ELSE '普通维修' END) as OperationType,
	null as ObuNoOld,
    CREATED_DATE OperationTime,
    AGENT.ISSUER_NAME AgentName,
    c.OPERATOR_NAME  operator
    FROM REPAIR_ORDERS AS A
    LEFT JOIN (SELECT ISSUER_NO,ISSUER_NAME FROM ISSUE_RPT.DBO.ISSUERS UNION ALL SELECT AGENT_NO,AGENT_NAME FROM ISSUE_RPT.DBO.ISSUER_AGENTS) AS AGENT ON 
    A.AGENT_NO=AGENT.ISSUER_NO
    LEFT JOIN ISSUE_RPT.DBO.OPERATORS AS c ON A.CREATED_BY=c.OPERATOR_NO " + userSql + " WHERE OBU_NO='" + item + "' AND STATUS!=-1 " + carSql.Replace("B", "A") + ") as a";
                List<View_ObuHistoryInfo> list2 = db.Database.SqlQuery<View_ObuHistoryInfo>(sql).ToList();



                if (list2.Count > 0)
                {
                    list.AddRange(list2);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取标签的旧标签号
        /// </summary>
        private List<string> GetAllHistoryObuNoByDate(string obuNoSql, string dateSql)
        {
            List<string> list = new List<string>();
            list.Add(obuNoSql);
            string sql = "select card_id from ISSUE_RPT..OPERATION_LOGS WHERE REMARK='" + obuNoSql + "' AND OPERATION_KIND IN (160,161,175,178,189,190) " + dateSql;
            string oldObuNo = db.Database.SqlQuery<string>(sql).FirstOrDefault();
            if (!string.IsNullOrEmpty(oldObuNo))
            {
                list.AddRange(GetAllHistoryObuNoByDate(oldObuNo, dateSql).ToList());
            }
            return list;
        }
        #endregion
    }
}
